package cn.edu.sanxiau.www.dao.impl;

import java.io.IOException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.sanxiau.www.dao.RoomCourseDao;
import cn.edu.sanxiau.www.model.RoomCourse;
import cn.edu.sanxiau.www.model.RoomExam;
import frame.utils.dbutil.JdbcUtils;

public class RoomCourseDaoImpl implements RoomCourseDao {

	@Override
	public List<RoomCourse> queryTimeByCourse() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// 查询数据库
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM course INNER JOIN room_course ON course.courseId = room_course.courseId ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			List<RoomCourse> roomCourse_db_list = new ArrayList<RoomCourse>();
			
			while (rs.next()) {
				RoomCourse roomCourse_db = new RoomCourse();
				roomCourse_db.setRoom_course_Id(rs.getInt("room_course_Id"));
				roomCourse_db.setCourseId(rs.getInt("courseId"));
				roomCourse_db.setRoomId(rs.getInt("roomId"));
				roomCourse_db.setTime(rs.getString("time"));
				roomCourse_db_list.add(roomCourse_db);
			}
			return roomCourse_db_list;
		} catch (ClassNotFoundException | IOException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int addRoomCourseByRoomCourse(RoomCourse roomCourse) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		
		try {
			conn = JdbcUtils.getConnection();
			String sql = "INSERT INTO  room_course (courseId,roomId,time,date) VALUES(?,?,?,?)";
			String sql1 = "UPDATE course SET state=? where courseId=?";
			String sql2 = "UPDATE classroom SET state=?, date=? where roomId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt1 = conn.prepareStatement(sql1);
			pstmt2 = conn.prepareStatement(sql2);
			
			pstmt.setInt(1, roomCourse.getCourseId());
			pstmt.setInt(2, roomCourse.getRoomId());
			pstmt.setString(3, roomCourse.getTime());
			pstmt.setString(4, roomCourse.getDate());
			
			pstmt1.setString(1, roomCourse.getTime());
			pstmt1.setInt(2, roomCourse.getCourseId());
			
			
			pstmt2.setString(1, roomCourse.getTime());
			pstmt2.setString(2, roomCourse.getDate());
			pstmt2.setInt(3, roomCourse.getRoomId());
			
			
			int n = pstmt.executeUpdate();
			pstmt1.executeUpdate();
			pstmt2.executeUpdate();

			return n;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int deleteRoomCourseByRoomCourseId(int roomCourseId) {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = JdbcUtils.getConnection();

			String sql = "DELETE FROM room_course WHERE room_course_Id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, roomCourseId);

			int m = pstmt.executeUpdate();
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	
	
	//////////////////////////////////////////////////////////////////////////////////////////////////
	@Override
	public int addRoomExamByRoomExam(RoomExam roomExam) {    //add//add
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		
		try {
			conn = JdbcUtils.getConnection();
			String sql = "INSERT INTO  room_exam (examId,roomId,time,date) VALUES(?,?,?,?)";
			String sql1 = "UPDATE exam SET examstate=? where examId=?";
			String sql2 = "UPDATE classroom SET examstate=?, date=? where roomId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt1 = conn.prepareStatement(sql1);
			pstmt2 = conn.prepareStatement(sql2);
			
			pstmt.setInt(1, roomExam.getExamId());
			pstmt.setInt(2, roomExam.getRoomId());
			pstmt.setString(3, roomExam.getTime());
			pstmt.setString(4, roomExam.getDate());
			
			pstmt1.setString(1, roomExam.getTime());
			pstmt1.setInt(2, roomExam.getExamId());
			
			
			pstmt2.setString(1, roomExam.getTime());
			pstmt2.setString(2, roomExam.getDate());
			pstmt2.setInt(3, roomExam.getRoomId());
			
			
			int n = pstmt.executeUpdate();
			pstmt1.executeUpdate();
			pstmt2.executeUpdate();

			return n;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

}
